package com.voicenews.model;

import java.net.MalformedURLException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;

public class DAO {
	public static String INSERT_ARTICLE = "INSERT INTO article"
			+ "(title, description, url, image_link, pubdate, feed_id, content) VALUES"
			+ "(?,?,?,?,?,?,?)";
	public static String INSERT_TOPIC = "INSERT INTO topic" + "(name) VALUES"
			+ "(?)";
	public static String INSERT_USER = "INSERT INTO user" + "(username) VALUES"
			+ "(?)";

	public static String updateFeed = "UPDATE feed SET last_refresh_time = ? WHERE id = ?";
	/*
	 * These variable values are used to setup the Connection object
	 */

	static final String URL = "jdbc:mysql://localhost:3307/theky";
	static final String USER = "theky";
	static final String PASSWORD = "chunt021090";
	static final String DRIVER = "com.mysql.jdbc.Driver";

	/*
	 * This method is used to create a connection using the values listed above.
	 * Notice the throws clause in the method signature. This allows the calling
	 * method to deal with the exception rather than catching it in both places.
	 * The ClassNotFoundException must be caught because the forName method
	 * requires it.
	 */

	public Connection getConnection() throws SQLException {
		Connection con = null;
		try {
			Class.forName(DRIVER);
			Properties props = new Properties();
			props.setProperty("user", "root");
			props.setProperty("password", "");
			props.setProperty("connectionCollation", "utf8_unicode_ci");
			con = DriverManager
					.getConnection(
							"jdbc:mysql://localhost:3306/voicenews?useUnicode=true&characterEncoding=UTF-8",
							props);
		} catch (ClassNotFoundException e) {
			System.out.println(e.getMessage());
			System.exit(-1);
		}
		return con;
	}

	/*
	 * This method does most of the work. Note the try and catch. Virtually all
	 * JDBC methods throw a SQLException that must be tended to. The Connection
	 * object is used to create a Statement object. The executeQuery method is
	 * used to submit a SELECT SQL query. The executeUpdate method can be used
	 * to delete, change, or insert records. The executeQuery method returns a
	 * ResultSet object. It contains methods to navigate through the records in
	 * the ResultSet object (the next method for example moves the cursor to the
	 * next row; it returns false when it runs out of rows) as well as methods
	 * to access fields in those rows. Notice that the id and job_id fields are
	 * long data types while name and location are Strings. The ResultSet object
	 * provides methods to deal with most common data types, but it&rsquo;s a
	 * good idea to review how Java data types align with database data types.
	 * The report is formatted using the format method introduced in Java 5.
	 */


	@SuppressWarnings({ "deprecation" })
	public void insertArticle(Article article, String content) {
		Connection connection = null;
		java.sql.PreparedStatement statement = null;
		ResultSet generatedKeys = null;

		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(INSERT_ARTICLE,
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, article.getTitle());
			statement.setString(2, article.getDescription());
			statement.setString(3, article.getUrl().toString());
			statement.setString(4, article.getImgLink());
			statement.setLong(5, new Date(article.getPubDate()).getTime());
			statement.setLong(6, article.getFeedId());
			statement.setString(7, content);

			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating user failed, no rows affected.");
			}

			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				article.setArticleId(generatedKeys.getLong(1));
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}

			System.out.println("id : " + article.getArticleId());
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}

	public void updateFeed(Feed feed) {
		Connection connection = null;
		java.sql.PreparedStatement statement = null;

		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(updateFeed);
			statement.setLong(1, Long.valueOf(feed.getLastRefreshTime()));
			statement.setLong(2, feed.getId());
			// ...

			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating user failed, no rows affected.");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

	public void searchByName(String string) {
		ResultSet rs = null;
		try {
			Statement s = getConnection().createStatement();
			rs = s.executeQuery("SELECT * FROM PERSON WHERE name = '" + string
					+ "'");
			System.out.format("%3s %-15s %-7s %-7s%n", "ID", "NANE", "JOB ID",
					"LOCATION");
			System.out.format("%3s %15s %7s %7s%n", "---", "---------------",
					"-------", "--------");

			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				long job = rs.getLong("job_id");
				String location = rs.getString("location");
				System.out.format("%-3d %-15s %7d %5s%n", id, name, job,
						location);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			System.exit(-1);
		}
	}

	public List<Feed> getFeeds() {
		ResultSet rs = null;
		List<Feed> feeds = new ArrayList<Feed>();
		try {
			Statement s = getConnection().createStatement();
			rs = s.executeQuery("SELECT * FROM feed");
			System.out.format("%3s %-15s %-7s %-7s%n", "ID", "NANE", "url",
					"LOCATION");
			System.out.format("%3s %15s %7s %7s%n", "---", "---------------",
					"-------", "--------");

			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				String url = rs.getString("url");
				long idTopic = rs.getLong("topic_id");
				long lastRefreshTime = rs.getLong("last_refresh_time");
				System.out.format("%-3d %-15s %7s %dn", id, name, url,
						lastRefreshTime);
				Feed feed = new Feed(id, url, lastRefreshTime, idTopic);
				feeds.add(feed);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return feeds;
		}

		return feeds;
	}

	public void getArticles() {
		ResultSet rs = null;
		try {
			Statement s = getConnection().createStatement();
			rs = s.executeQuery("SELECT * FROM article");
			System.out.format("%3s %-15s %-7s %-7s%n", "ID", "NANE", "url",
					"LOCATION");
			System.out.format("%3s %15s %7s %7s%n", "---", "---------------",
					"-------", "--------");

			while (rs.next()) {
				long id = rs.getLong("id");
				String title = rs.getString("title");
				String description = rs.getString("description");
				System.out.format("%-3d %-15s %7s%n", id, title, description);
			}
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		}

	}

//	public List<Article> getArticlesByTopic(String topic, int offset) {
//		Connection connection = null;
//		List<Article> articles = new ArrayList<Article>();
//		String query = "select * from article where feed_id in (select id from feed where topic_id in (select id from topic where topic.name like '"
//				+ topic + "')) order by pubdate desc limit 10 offset " + offset;
//		System.out.println(query);
//		java.sql.PreparedStatement statement = null;
//		try {
//			connection = getConnection();
//			statement = connection.prepareStatement(query);
//			// prepared.setString(1, topic);
//			// prepared.setLong(2, offset);
//			ResultSet result = statement.executeQuery(query);
//			while (result.next()) {
//				Article article = new Article();
//				article.setArticleId(result.getLong("id"));
//				article.setTitle(result.getString("title"));
//				article.setDescription(result.getString("description"));
//				article.setUrl(new java.net.URL(result.getString("url")));
//				article.setImgLink(result.getString("image_link"));
//				article.setPubDate(String.valueOf(result.getLong("pubdate")));
//				article.setFeedId(result.getLong("feed_id"));
//				String title = result.getString("title");
//				String date = new Date(result.getLong("pubdate")).toString();
//				System.out.println(title + "\t" + date);
//				articles.add(article);
//
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} catch (MalformedURLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//			if (statement != null)
//				try {
//					statement.close();
//				} catch (SQLException logOrIgnore) {
//				}
//			if (connection != null)
//				try {
//					connection.close();
//				} catch (SQLException logOrIgnore) {
//				}
//		}
//		return articles;
//	}

	public void insertTopic(String name) {
		Connection connection = null;
		java.sql.PreparedStatement statement = null;
		ResultSet generatedKeys = null;

		try {
			System.out.println(INSERT_TOPIC);
			connection = this.getConnection();
			statement = connection.prepareStatement(INSERT_TOPIC,
					Statement.RETURN_GENERATED_KEYS);
			statement.setString(1, name);
			System.out.println(name);
			// ...

			int affectedRows = statement.executeUpdate();
			if (affectedRows == 0) {
				throw new SQLException(
						"Creating user failed, no rows affected.");
			}

			generatedKeys = statement.getGeneratedKeys();
			if (generatedKeys.next()) {
				System.out.println(generatedKeys.getLong(1));
			} else {
				throw new SQLException(
						"Creating user failed, no generated key obtained.");
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}
	}

	public String registerUser(String username) {
		Connection connection = null;
		java.sql.PreparedStatement statement = null;
		ResultSet generatedKeys = null;
		String id = null;
		String queryString = "select id from user where username = '"
				+ username + "'";
		ResultSet result = null;

		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(queryString);

			result = statement.executeQuery(queryString);
			if (result.next()) {
				id = String.valueOf(result.getLong("id"));
			} else {
				statement = connection.prepareStatement(INSERT_USER,
						Statement.RETURN_GENERATED_KEYS);
				statement.setString(1, username);

				int affectedRows = statement.executeUpdate();
				if (affectedRows == 0) {
					throw new SQLException(
							"Creating user failed, no rows affected.");
				}

				generatedKeys = statement.getGeneratedKeys();
				if (generatedKeys.next()) {
					id = String.valueOf(generatedKeys.getLong(1));
				} else {
					throw new SQLException(
							"Creating user failed, no generated key obtained.");
				}
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (generatedKeys != null)
				try {
					generatedKeys.close();
				} catch (SQLException logOrIgnore) {
				}
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

		return id;

	}

	public String findIdUserByUsername(String username) {
		String queryString = "select id from user where username = '"
				+ username + "'";
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;
		String id = null;

		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(queryString);

			result = statement.executeQuery(queryString);
			if (result.next()) {
				id = String.valueOf(result.getLong("id"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}
		return id;

	}

//	public String getContentOfArticle(String url) {
//		String queString = "select content from article where url = '" + url
//				+ "'";
//		Connection connection = null;
//		Statement statement = null;
//		ResultSet result = null;
//		String content = null;
//
//		try {
//			connection = this.getConnection();
//			statement = connection.prepareStatement(queString);
//
//			result = statement.executeQuery(queString);
//			if (result.next()) {
//				content = result.getString("content");
//			}
//
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		} finally {
//			if (statement != null)
//				try {
//					statement.close();
//				} catch (SQLException logOrIgnore) {
//				}
//			if (connection != null)
//				try {
//					connection.close();
//				} catch (SQLException logOrIgnore) {
//				}
//		}
//		return content;
//	}

	public void addArticleToUser(String idUser, String urlString) {
		// TODO Auto-generated method stub
		String queString = "select id from article where url = '" + urlString
				+ "'";
		String INSERT_USER_ARTICLE = "INSERT INTO user_article"
				+ "(user_id, article_id) VALUES" + "(?,?)";
		Connection connection = null;
		Statement statement = null;
		java.sql.PreparedStatement st = null;
		ResultSet result = null;
		String idArticle = null;
		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(queString);

			result = statement.executeQuery(queString);
			if (result.next()) {
				idArticle = String.valueOf(result.getLong("id"));
				st = connection.prepareStatement(INSERT_USER_ARTICLE);
				st.setString(1, idUser);
				st.setString(2, idArticle);
				st.executeUpdate();
			} else {
				return;
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (st != null) {
				try {
					st.close();
				} catch (SQLException ex) {

				}
			}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

	}

	public List<Article> getRecommendArticle(String idUser) {
		// TODO Auto-generated method stub
		List<Article> articles = new ArrayList<Article>();
		String query = "select feed_id, count(feed_id) as 'amount' from article where id in (select article_id from user_article where user_id = "
				+ idUser + ") group by feed_id order by amount desc";
		String queryArticles = "select * from article where feed_id = (?) order by pubdate desc limit 20";
		// String query = "select id from topic where name like 'xã hội'";
		System.out.println(query);

		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;
		String idFeed = null;
		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(query);

			result = statement.executeQuery(query);
			while (result.next()) {
				idFeed = String.valueOf(result.getLong("feed_id"));
				break;
			}
			java.sql.PreparedStatement prepareStatement = connection
					.prepareStatement(queryArticles);
			prepareStatement.setString(1, idFeed);
			ResultSet articlesResultSet = prepareStatement.executeQuery();
			while (articlesResultSet.next()) {
				Article article = new Article();
				article.setArticleId(articlesResultSet.getLong("id"));
				article.setTitle(articlesResultSet.getString("title"));
				article.setDescription(articlesResultSet
						.getString("description"));
				article.setUrl(new java.net.URL(articlesResultSet
						.getString("url")));
				article.setImgLink(articlesResultSet.getString("image_link"));
				article.setPubDate(String.valueOf(articlesResultSet
						.getLong("pubdate")));
				article.setFeedId(articlesResultSet.getLong("feed_id"));
				String title = articlesResultSet.getString("title");
				String date = new Date(articlesResultSet.getLong("pubdate"))
						.toString();
				System.out.println(title + "\t" + date);
				articles.add(article);

			}

		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}

			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

		return articles;
	}

	public String receiveRegistrationGCMFromClient(String idClient, String idGCM) {
		// TODO Auto-generated method stub
		String insertGCMRegistrationToUser = "update user set id_gcm = '" + idGCM + "' WHERE id = " + idClient;
		System.out.println(insertGCMRegistrationToUser);
		Connection connection = null;
		java.sql.PreparedStatement statement = null;

		try {
			connection = this.getConnection();
			statement = connection.prepareStatement(insertGCMRegistrationToUser);
			System.out.println(statement.executeUpdate());
		} catch (SQLException e) {
			// TODO: handle exception
			System.out.println(e.getMessage());
			return "false";
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException logOrIgnore) {
				}
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException logOrIgnore) {
				}
		}

		return "true";
	}
	
	public String registUserTopic(String name, String userId) {
		name = name.toLowerCase();
		System.out.println(name);
		String query = "select id from topic where name = '" + name + "'";
		Connection connection = null;
		Statement statement = null;
		ResultSet result = null;
		String id = null;
		try {
			connection = this.getConnection();
			statement = connection.createStatement();
			result = statement.executeQuery(query);
			if (result.next()) {
				id = String.valueOf(result.getLong("id"));
			}
			String queryUpdate = "update user set id_topic_registration = " + id + " where id = " + userId;
			java.sql.PreparedStatement preparedStatement = connection.prepareStatement(queryUpdate);
			preparedStatement.executeUpdate();

			
		} catch (SQLException e) {
			System.out.println(e.getMessage());
			return "false";
		} finally {
			if (statement != null)
				try {
					statement.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			
			if (connection != null)
				try {
					connection.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		return id;
	}

}